﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;

using System.Web.Mvc;


namespace BookStore.Admin.Models
{
    public class BookModel
    {
        private string connectionString;
        private SqlConnection connect;

        public BookModel()
        {
            connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString;
            connect = new SqlConnection(connectionString);
        }

        public string getBookURLImage(int ID)
        {
            string result = "";
            try
            {
                connect.Open();
                string selectSQL = "SELECT * FROM Book WHERE book_id = " + ID.ToString();
                SqlCommand cmd = new SqlCommand(selectSQL, connect);

                SqlDataReader reader;
                reader = cmd.ExecuteReader();
                if (reader.Read())
                    result = reader["image_url"].ToString();
                
                reader.Close();
            }
            catch (Exception err)
            {
                return null;
            }
            finally
            {
                connect.Close();
            }

            return result;
        }

        public string[] getBookbyID(int ID)
        {
            string[] result = new string[13];
            try
            {
                connect.Open();
                string selectSQL = "SELECT * FROM Book WHERE book_id = " + ID.ToString();
                SqlCommand cmd = new SqlCommand(selectSQL, connect);

                SqlDataReader reader;
                reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    result[0] = reader["book_code"].ToString();
                    result[1] = reader["book_name"].ToString();
                    result[2] = reader["author_name"].ToString();
                    result[3] = reader["price_buy"].ToString();
                    result[4] = reader["price_rent"].ToString();
                    result[5] = reader["quantity"].ToString();
                    result[6] = reader["book_id"].ToString();
                    result[7] = reader["image_url"].ToString();
                    result[8] = reader["status"].ToString(); 
                    result[9] = reader["short_description"].ToString() != "" ? reader["short_description"].ToString() : "Đang Cập Nhật !";
                    result[10] = reader["long_description"].ToString() != "" ? reader["long_description"].ToString() : "Đang Cập Nhật !";
                    result[11] = reader["category_id"].ToString();
                    result[12] = reader["quantity_rent"].ToString();
                }
                reader.Close();
            }
            catch (Exception err)
            {
                return null;
            }
            finally
            {
                connect.Close();
            }

            return result;
        }

        public string[][] getAllBook(int page, int offset, string sort, string type, string keyword)
        {
            string[][] result = new string[12][];
            try
            {
                connect.Open();

                string order_by = "";
                if (sort == "bn")
                    order_by += "B.book_name " + type;
                if (sort == "bc")
                    order_by += "B.book_code " + type;
                if (sort == "pb")
                    order_by += "B.price_buy " + type;
                if (sort == "pr")
                    order_by += "B.price_rent " + type;
                if (sort == "qb")
                    order_by += "B.quantity " + type;
                if (sort == "qr")
                    order_by += "B.quantity_rent " + type;
                if (sort == "st")
                    order_by += "B.status " + type;
                if (sort == "cn")
                    order_by += "C.category_name " + type;

                string where = "";
                if (keyword.Trim() != "") where = " WHERE B.book_code LIKE N'%" + keyword.Trim() + "%'";

                string selectSQL = "WITH LIMIT AS(SELECT B.*,C.category_name AS category_name, ROW_NUMBER() OVER(ORDER BY " + order_by + ") AS rowNum FROM Book B INNER JOIN Categories C ON(B.category_id=C.category_id)" + where + ") ";
                selectSQL += "SELECT * FROM LIMIT WHERE rowNum BETWEEN " + ((page - 1) * offset + 1) + " AND " + (page * offset);
                SqlCommand cmd = new SqlCommand(selectSQL, connect);

                SqlDataReader reader;
                reader = cmd.ExecuteReader();
                int i = 0;
                while (reader.Read())
                {
                    result[i] = new string[12];
                    result[i][0] = reader["book_code"].ToString();
                    result[i][1] = reader["book_name"].ToString();
                    result[i][2] = reader["author_name"].ToString();
                    result[i][3] = reader["price_buy"].ToString() + " VNĐ";
                    result[i][4] = reader["price_rent"].ToString() + " VNĐ";
                    result[i][5] = reader["quantity"].ToString();
                    result[i][6] = reader["book_id"].ToString();
                    result[i][7] = reader["image_url"].ToString() != "" ? reader["image_url"].ToString() : "/content/images/no_image.jpg";
                    result[i][8] = new DateTime(1970, 1, 1).AddSeconds(Convert.ToInt32(reader["updated_date"].ToString())).ToString("dd-MM-yyyy");
                    result[i][9] = reader["quantity_rent"].ToString();
                    result[i][10] = Convert.ToInt32(reader["status"]) == 1 ? "Enabled" : "Disabled";
                    result[i][11] = reader["category_name"].ToString();
                    i++;
                }
                reader.Close();
            }
            catch (Exception err)
            {
                return null;
            }
            finally
            {
                connect.Close();
            }

            return result;
        }

        public int countAllBook()
        {
            int result = 0;
            try
            {
                connect.Open();
                string selectSQL = "SELECT count(*) as total_book FROM Book";
                SqlCommand cmd = new SqlCommand(selectSQL, connect);

                SqlDataReader reader;
                reader = cmd.ExecuteReader();
                if (reader.Read())
                    result = Convert.ToInt32(reader["total_book"]);

                reader.Close();
            }
            catch (Exception err)
            {
                return 0;
            }
            finally
            {
                connect.Close();
            }

            return result;
        }

        public int checkBookCode(int id,string code)
        {
            int result = 0;
            try
            {
                connect.Open();
                string selectSQL = "SELECT count(*) as total_book FROM Book WHERE book_code = N'" + code +"' AND book_id != " + id.ToString();
                SqlCommand cmd = new SqlCommand(selectSQL, connect);

                SqlDataReader reader;
                reader = cmd.ExecuteReader();
                if (reader.Read())
                    result = Convert.ToInt32(reader["total_book"]);

                reader.Close();
            }
            catch (Exception err)
            {
                return 0;
            }
            finally
            {
                connect.Close();
            }

            return result;
        }

        public int deleteBook(int id)
        {
            int result = 0;
            try
            {
                connect.Open();

                string selectSQL = "DELETE FROM Favorite_book WHERE book_id = " + id.ToString();
                SqlCommand cmd = new SqlCommand(selectSQL, connect);
                cmd.ExecuteNonQuery();

                selectSQL = "DELETE FROM Comment WHERE book_id = " + id.ToString();
                cmd = new SqlCommand(selectSQL, connect);
                cmd.ExecuteNonQuery();

                selectSQL = "DELETE FROM Book WHERE book_id = " + id.ToString();
                cmd = new SqlCommand(selectSQL, connect);
                cmd.ExecuteNonQuery();
            }
            catch (Exception err)
            {
                if (err.Message.IndexOf("FK_Bill_Rent_Detail_Book") != -1)
                    return -5;
                if (err.Message.IndexOf("FK_Bill_detail_Book") != -1)
                    return -6;
                return -1;
            }
            finally
            {
                connect.Close();
            }

            return 1;
        }

        public int editBook(string [] data)
        {
            int result = 0;
            try
            {
                connect.Open();

                string selectSQL = "UPDATE Book";
                selectSQL += " SET book_code = N'" + data[0] + "', ";
                selectSQL += " book_name = N'" + data[1] + "', ";
                selectSQL += " author_name = N'" + data[2] + "', ";
                selectSQL += " price_buy = " + data[3] + ", ";
                selectSQL += " price_rent = " + data[4] + ", ";
                selectSQL += " quantity = " + data[5] + ", ";
                selectSQL += " quantity_rent = " + data[6] + ", ";
                selectSQL += " image_url = N'" + data[7] + "', ";
                selectSQL += " status = " + data[8] + ", ";
                selectSQL += " short_description = N'" + data[9] + "', ";
                selectSQL += " long_description = N'" + data[10] + "', ";
                selectSQL += " updated_date = " + Utils.Now() + ", ";
                selectSQL += " category_id = " + data[11];
                selectSQL += " WHERE book_id = " + data[12];
                SqlCommand cmd = new SqlCommand(selectSQL, connect);
                cmd.ExecuteNonQuery();

            }
            catch (Exception err)
            {
                return -1;
            }
            finally
            {
                connect.Close();
            }

            return 1;
        }

        public int newBook(string[] data)
        {
            int result = 0;
            try
            {
                connect.Open();

                string selectSQL = "INSERT INTO Book";
                selectSQL += " (book_code,book_name,author_name,price_buy,price_rent,quantity,quantity_rent,image_url,status,short_description,long_description,category_id,created_date,updated_date) ";
                selectSQL += " VALUES (N'" + data[0] + "', ";
                selectSQL += "N'" + data[1] + "', ";
                selectSQL += "N'" + data[2] + "', ";
                selectSQL += data[3] + ", ";
                selectSQL += data[4] + ", ";
                selectSQL += data[5] + ", ";
                selectSQL += data[6] + ", ";
                selectSQL += "N'" + data[7] + "', ";
                selectSQL += data[8] + ", ";
                selectSQL += "N'" + data[9] + "', ";
                selectSQL += "N'" + data[10] + "', ";
                selectSQL += data[11] + ", ";
                selectSQL += Utils.Now() + ", ";
                selectSQL += Utils.Now() + ")";
                SqlCommand cmd = new SqlCommand(selectSQL, connect);
                cmd.ExecuteNonQuery();

            }
            catch (Exception err)
            {
                return -2;
            }
            finally
            {
                connect.Close();
            }

            return 2;
        }
        public string[][] getTopBuyBook(int tu, int den)
        {
            string[][] result = new string[100][];
            string[] temp = new string[100];
            int i=0,j=0;
            int total = 0;
            string book_name = "";
            try
            {
                connect.Open();
                  string selectSQL="";
                if (tu != 0 && den != 0)
                {
                    selectSQL = "SELECT distinct bd.book_id FROM Bill_Detail bd join Bill b on bd.bill_id=b.bill_id where b.status=3 and b.created_date between "+ tu +" and "+den;
                }
                else
                    selectSQL = "SELECT distinct bd.book_id FROM Bill_Detail bd join Bill b on bd.bill_id=b.bill_id where b.status=3";
                SqlCommand cmd = new SqlCommand(selectSQL, connect);
                SqlDataReader reader;
                reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    temp[i] = reader["book_id"].ToString();
                    i++;
                }
                reader.Close();

                foreach (string te in temp)
                {
                    if (te != null)
                    {
                        total = 0;
                        book_name = "";
                        selectSQL = "SELECT book_name FROM Book where book_id=" + te;
                        cmd = new SqlCommand(selectSQL, connect);

                        reader = cmd.ExecuteReader();
                        if (reader.Read())
                        {
                            book_name = reader["book_name"].ToString();
                        }
                        reader.Close();
                        if (tu != 0 && den != 0)
                        {
                            selectSQL = "SELECT distinct bd.quantity FROM Bill_Detail bd join Bill b on bd.bill_id=b.bill_id where b.created_date between " + tu + " and " + den + " and bd.book_id=" + te;
                        }
                        else selectSQL = "SELECT distinct quantity FROM Bill_Detail where book_id=" + te;
                             cmd = new SqlCommand(selectSQL, connect);

                        reader = cmd.ExecuteReader();
                        while (reader.Read())
                        {
                            total += Convert.ToInt32(reader["quantity"]);
                        }
                        reader.Close();
                        result[j] = new string[3];
                        result[j][0] = te;
                        result[j][1] = total.ToString();
                        result[j][2] = book_name;
                        j++;
                    }
                    else break;
                }

            }
            catch (Exception err)
            {
                return null;
            }
            finally
            {
                connect.Close();
            }
            
         return result;
        }

        public string[][] getTopRentBook(int tu, int den)
        {
            string[][] result = new string[100][];
            string[] temp = new string[100];
            int i = 0, j = 0;
            int total = 0;
            string book_name = "";
            try
            {
                connect.Open();
                string selectSQL = "";
                if (tu != 0 && den != 0)
                {
                    selectSQL = "SELECT distinct bd.book_id FROM Bill_Rent_Detail bd join Bill_Rent b on bd.bill_id=b.bill_id where b.status=3 and b.created_date between " + tu + " and " + den ;
                }
                else
                    selectSQL = "SELECT distinct bd.book_id FROM Bill_Rent_Detail bd join Bill_Rent b on bd.bill_id=b.bill_id where b.status=3";
               
                SqlCommand cmd = new SqlCommand(selectSQL, connect);
                SqlDataReader reader;
                reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    temp[i] = reader["book_id"].ToString();
                    i++;
                }
                reader.Close();

                foreach (string te in temp)
                {
                    if (te != null)
                    {
                        total = 0;
                        book_name = "";
                        selectSQL = "SELECT book_name FROM Book where book_id=" + te;
                        cmd = new SqlCommand(selectSQL, connect);

                        reader = cmd.ExecuteReader();
                        if (reader.Read())
                        {
                            book_name = reader["book_name"].ToString();
                        }
                        reader.Close();
                        if (tu != 0 && den != 0)
                        {
                            selectSQL = "SELECT  bd.quantity FROM Bill_Rent_Detail bd join Bill_Rent b on bd.bill_id=b.bill_id where b.created_date between " + tu + " and " + den + " and bd.book_id=" + te;
                        }
                        else selectSQL = "SELECT quantity FROM Bill_Rent_Detail where book_id=" + te;
                             cmd = new SqlCommand(selectSQL, connect);

                        reader = cmd.ExecuteReader();
                        while (reader.Read())
                        {
                            total += Convert.ToInt32(reader["quantity"]);
                        }
                        reader.Close();
                        result[j] = new string[3];
                        result[j][0] = te;
                        result[j][1] = total.ToString();
                        result[j][2] = book_name;
                        j++;
                    }
                    else break;
                }

            }
            catch (Exception err)
            {
                return null;
            }
            finally
            {
                connect.Close();
            }

            return result;
        }

        public string[][] getTopFavoriteBook(int tu, int den)
        {
            string[][] result = new string[100][];
            string[] temp = new string[100];
            int i = 0, j = 0;
            int total = 0;
            string book_name = "";
            try
            {
                connect.Open();
                string selectSQL = "";
                if (tu != 0 && den != 0)
                {
                    selectSQL = "SELECT distinct book_id FROM Favorite_Book where created_date between " + tu + " and " + den;
                }
                else
                    selectSQL = "SELECT distinct book_id FROM Favorite_Book";
               
               
                SqlCommand cmd = new SqlCommand(selectSQL, connect);
                SqlDataReader reader;
                reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    temp[i] = reader["book_id"].ToString();
                    i++;
                }
                reader.Close();

                foreach (string te in temp)
                {
                    if (te != null)
                    {
                        total = 0;
                        book_name = "";
                        selectSQL = "SELECT book_name FROM Book where book_id=" + te;
                        cmd = new SqlCommand(selectSQL, connect);

                        reader = cmd.ExecuteReader();
                        if (reader.Read())
                        {
                            book_name = reader["book_name"].ToString();
                        }
                        reader.Close();

                        if (tu != 0 && den != 0)
                        {
                            selectSQL = "SELECT Count(*) as total FROM Favorite_Book where book_id=" + te + " and created_date between " + tu + " and " + den;
                        }
                        else selectSQL = "SELECT Count(*) as total FROM Favorite_Book where book_id=" + te ;
                        cmd = new SqlCommand(selectSQL, connect);

                        reader = cmd.ExecuteReader();
                        while (reader.Read())
                        {
                            total += Convert.ToInt32(reader["total"]);
                        }
                        reader.Close();
                        result[j] = new string[3];
                        result[j][0] = te;
                        result[j][1] = total.ToString();
                        result[j][2] = book_name;
                        j++;
                    }
                    else break;
                }

            }
            catch (Exception err)
            {
                return null;
            }
            finally
            {
                connect.Close();
            }

            return result;
        }

        public string[][] getTopCommentBook(int tu, int den)
        {
            string[][] result = new string[100][];
            string[] temp = new string[100];
            int i = 0, j = 0;
            int total = 0;
            string book_name = "";
            try
            {
                connect.Open();
                string selectSQL = "";
                if (tu != 0 && den != 0)
                {
                    selectSQL = "SELECT distinct book_id FROM Comment where created_date between " + tu + " and " + den;
                }
                else
                    selectSQL = "SELECT distinct book_id FROM Comment";
                
                SqlCommand cmd = new SqlCommand(selectSQL, connect);
                SqlDataReader reader;
                reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    temp[i] = reader["book_id"].ToString();
                    i++;
                }
                reader.Close();

                foreach (string te in temp)
                {
                    if (te != null)
                    {
                        total = 0;
                        book_name = "";
                        selectSQL = "SELECT book_name FROM Book where book_id=" + te;
                        cmd = new SqlCommand(selectSQL, connect);

                        reader = cmd.ExecuteReader();
                        if (reader.Read())
                        {
                            book_name = reader["book_name"].ToString();
                        }
                        reader.Close();
                        if (tu != 0 && den != 0)
                        {
                            selectSQL = "SELECT Count(*) as total FROM Comment where book_id=" + te + " and created_date between " + tu + " and " + den;
                        }
                        else selectSQL = "SELECT Count(*) as total FROM Comment where book_id=" + te ;
                             cmd = new SqlCommand(selectSQL, connect);

                        reader = cmd.ExecuteReader();
                        while (reader.Read())
                        {
                            total += Convert.ToInt32(reader["total"]);
                        }
                        reader.Close();
                        result[j] = new string[3];
                        result[j][0] = te;
                        result[j][1] = total.ToString();
                        result[j][2] = book_name;
                        j++;
                    }
                    else break;
                }

            }
            catch (Exception err)
            {
                return null;
            }
            finally
            {
                connect.Close();
            }

            return result;
        }
        public string getTotalBook(int tu, int den)
        {
            string type_user = "";
            try
            {
                connect.Open();
                
                string selectSQL_1 = "";
                if (tu != 0 && den != 0)
                {
                    selectSQL_1 = "select Count(*) as total from Book where created_date between 0 and " + den;
                }
                else
                    selectSQL_1 = "select Count(*) as total from Book";
                
                
                
                SqlCommand cmd_1 = new SqlCommand(selectSQL_1, connect);
                SqlDataReader reader;
                reader = cmd_1.ExecuteReader();

                if (reader.Read())
                    type_user = Convert.ToInt32(reader["total"]).ToString();

                reader.Close();
            }
            catch (Exception err)
            {
                return "-1";
            }
            finally
            {
                connect.Close();
            }
            return type_user;
        }
        public string getTotalBookSell(int tu, int den)
        {
            int total = 0;
            try
            {
                connect.Open();
                string selectSQL_1="";


                if (tu != 0 && den != 0)
                {
                    selectSQL_1 = "select bd.quantity from Bill_Detail bd join Bill b on bd.bill_id=b.bill_id where b.status=3 and b.created_date between " + tu + " and " + den;
                }
                else
                    selectSQL_1 = "select bd.quantity from Bill_Detail bd join Bill b on bd.bill_id=b.bill_id where b.status=3 ";
                
                
                SqlCommand cmd_1 = new SqlCommand(selectSQL_1, connect);
                SqlDataReader reader;
                reader = cmd_1.ExecuteReader();

                while (reader.Read())
                    total += Convert.ToInt32(reader["quantity"]);

                reader.Close();
            }
            catch (Exception err)
            {
                return "-1";
            }
            finally
            {
                connect.Close();
            }
            return total.ToString();
        }

        public string getTotalSell(int tu, int den)
        {
            int total = 0;
            try
            {
                connect.Open();
                string selectSQL_1;

                if (tu != 0 && den != 0)
                {
                    selectSQL_1 = "select b.total_bill_price from Bill_Detail bd join Bill b on bd.bill_id=b.bill_id where b.created_date between " + tu + " and " + den;
                }
                else
                    selectSQL_1 = "select b.total_bill_price from Bill_Detail bd join Bill b on bd.bill_id=b.bill_id where b.status=3";
                
                SqlCommand cmd_1 = new SqlCommand(selectSQL_1, connect);
                SqlDataReader reader;
                reader = cmd_1.ExecuteReader();

                while(reader.Read())
                    total += Convert.ToInt32(reader["total_bill_price"]);

                reader.Close();
            }
            catch (Exception err)
            {
                return "-1";
            }
            finally
            {
                connect.Close();
            }
            return total.ToString();
        }
        public string getTotalRent(int tu, int den)
        {
            int total = 0;
            try
            {
                connect.Open();
                string selectSQL_1="";
                if (tu != 0 && den != 0)
                {
                    selectSQL_1 = "select b.total_bill_price,b.deposit from Bill_Rent_Detail bd join Bill_Rent b on bd.bill_id=b.bill_id where b.status=3 b.created_date between " + tu + " and " + den;
                }
                else
                    selectSQL_1 = "select b.total_bill_price,b.deposit from Bill_Rent_Detail bd join Bill_Rent b on bd.bill_id=b.bill_id where b.status=3";
                
                SqlCommand cmd_1 = new SqlCommand(selectSQL_1, connect);
                SqlDataReader reader;
                reader = cmd_1.ExecuteReader();

                while (reader.Read())
                    total += (Convert.ToInt32(reader["total_bill_price"]) - Convert.ToInt32(reader["deposit"]));

                reader.Close();
            }
            catch (Exception err)
            {
                return "-1";
            }
            finally
            {
                connect.Close();
            }
            return total.ToString();
        }

        public int updateQuanityBook(string sl,string id)
        {
            int result = 0;
            try
            {
                connect.Open();

                string selectSQL = "UPDATE Book set";
                selectSQL += " quantity = " + sl;
                selectSQL += " WHERE book_id = " + id;
                SqlCommand cmd = new SqlCommand(selectSQL, connect);
                cmd.ExecuteNonQuery();

            }
            catch (Exception err)
            {
                return -1;
            }
            finally
            {
                connect.Close();
            }

            return 1;
        }
        public int updateQuanityRentBook(string sl, string id)
        {
            int result = 0;
            try
            {
                connect.Open();

                string selectSQL = "UPDATE Book set";
                selectSQL += " quantity_rent = " + sl;
                selectSQL += " WHERE book_id = " + id;
                SqlCommand cmd = new SqlCommand(selectSQL, connect);
                cmd.ExecuteNonQuery();

            }
            catch (Exception err)
            {
                return -1;
            }
            finally
            {
                connect.Close();
            }

            return 1;
        }
        public int getQuantityBookByID(int ID)
        {
            int result=0;
            try
            {
                connect.Open();
                string selectSQL = "SELECT * FROM Book WHERE book_id = " + ID.ToString();
                SqlCommand cmd = new SqlCommand(selectSQL, connect);

                SqlDataReader reader;
                reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    result = Convert.ToInt32(reader["quantity"].ToString());  
                }
                reader.Close();
            }
            catch (Exception err)
            {
                return 0;
            }
            finally
            {
                connect.Close();
            }

            return result;
        }
        public int getQuantityRentBookByID(int ID)
        {
            int result = 0;
            try
            {
                connect.Open();
                string selectSQL = "SELECT * FROM Book WHERE book_id = " + ID.ToString();
                SqlCommand cmd = new SqlCommand(selectSQL, connect);

                SqlDataReader reader;
                reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    result = Convert.ToInt32(reader["quantity_rent"].ToString());
                }
                reader.Close();
            }
            catch (Exception err)
            {
                return 0;
            }
            finally
            {
                connect.Close();
            }

            return result;
        }
    }
}
